package com.example.exam.dao;

import com.example.exam.entity.Score;
import com.example.exam.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 成绩数据访问对象
 */
public class ScoreDAO {
    private QueryRunner queryRunner = new QueryRunner();
    
    // 获取学生的所有成绩
    public List<Score> getScoresByStudentId(String studentId) {
        String sql = "SELECT * FROM scores WHERE student_id = ? ORDER BY exam_date DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Score.class), studentId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    // 根据考试ID获取学生成绩
    public Score getScoreByExamId(String studentId, int examId) {
        String sql = "SELECT * FROM scores WHERE student_id = ? AND exam_id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanHandler<>(Score.class), studentId, examId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    // 获取学生的挂科成绩
    public List<Score> getFailedScores(String studentId) {
        String sql = "SELECT * FROM scores WHERE student_id = ? AND score < 60 ORDER BY exam_date DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Score.class), studentId);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    // 添加考试成绩
    public boolean addScore(Score score) {
        String sql = "INSERT INTO scores (student_id, student_name, exam_id, course_name, score, exam_date, status) " +
                     "VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection()) {
            int rows = queryRunner.update(conn, sql, 
                    score.getStudentId(),
                    score.getStudentName(),
                    score.getExamId(),
                    score.getCourseName(),
                    score.getScore(),
                    score.getExamDate(),
                    score.getStatus());
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}    